package cn.net.autocode.platform.excel;

import cn.hutool.core.util.StrUtil;
import cn.net.autocode.core.bean.ResultT;
import cn.net.autocode.dbManager.Dao;
import cn.net.autocode.dbManager.IdWorker;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import jakarta.servlet.http.HttpServletRequest;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("platform/excelTemplate")
public class PlatformExcelTemplate {
    private final Dao dao;
    private final Dao dictDao;

    public PlatformExcelTemplate(Dao dao, @Qualifier("dictDao") Dao dictDao) {
        this.dao = dao;
        this.dictDao = dictDao;
    }

    @RequestMapping("save")
    @Transactional
    public ResultT save(@RequestBody JSONObject param){
        if(!param.containsKey("id")|| StrUtil.isNotBlank(param.getString("id"))){
            param.put("id", IdWorker.ulid());
            //判定code是否存在
            int num = dao.queryValue("select count(*) from sys_excel_template where code=?",Integer.class,param.getString("code"));
            if(num>0){
                return ResultT.fail("Excel模板【"+param.getString("code")+"】已经存在，不能重复添加！");
            }
            dao.createJSON(param,"sys_excel_template");
            //生成模板元素
            List<String> codes = new ArrayList<>();
            List<String> labels = new ArrayList<>();
            String mainTable = param.getString("mainTable");
            JSONArray fields = dictDao.queryJSONArray("select t1.* from ac_table_fields t1,ac_table t2 where t1.table_id=t2.id and t2.table_name=? order by t1.sort_no",mainTable);
            for (int i=0;i<fields.size();i++){
                //excel_template_field
                JSONObject json = fields.getJSONObject(i);
                JSONObject field = new JSONObject();
                field.put("templateCode",param.getString("code"));
                //处理列编码，防治存在重复信息，不能导入
                String orginalcode = StrUtil.toCamelCase(json.getString("name"));
                String code = orginalcode;
                for(int index=1;codes.contains(code);index++){
                    code=orginalcode+index;
                }
                codes.add(code);
                field.put("code", code);

                //处理列标题，防治存在重复信息，不能导入
                String orginallabel = json.getString("remarks");
                String label = orginallabel;
                for(int index=1;labels.contains(label);index++){
                    label=orginallabel+index;
                }
                if(StrUtil.isBlank(label)){
                    labels.add(code);
                    field.put("label", code);
                }else{
                    labels.add(label);
                    field.put("label", label);
                }

                field.put("tableName", mainTable);
                field.put("columnName", json.getString("name"));
                field.put("sortNo", json.getIntValue("sortNo"));
                String isnullable = json.getString("isnullable");
                if("N".equals(isnullable)){
                    field.put("isRequired", "Y");
                }else{
                    field.put("isRequired", "N");
                }

                //处理数据类型
                String type = json.getString("type");
                if("INT".equalsIgnoreCase(type)||"DECIMAL".equalsIgnoreCase(type)){
                    field.put("dataType", "number");
                }else if("DATETIME".equalsIgnoreCase(type)){
                    field.put("dataType", "datetime");
                }else{
                    field.put("dataType", "text");
                }

                field.put("id",IdWorker.ulid());
                dao.createJSON(field,"sys_excel_template_field");
            }
        }else{
            dao.updateJSON(param,"sys_excel_template");
        }
        return ResultT.success(param);
    }

    @RequestMapping("createTemplate")
    public ResponseEntity<byte[]> createTemplate(@RequestBody JSONObject param){
        String templateCode = param.getString("templateCode");
        JSONObject template = dao.queryJSON("select * from sys_excel_template where code=?",templateCode);
        JSONArray excelHeaders = dao.queryJSONArray("select * from sys_excel_template_field where template_code=? order by sort_no",templateCode);


        Workbook workbook = new XSSFWorkbook();

        /**
         * 通过setExcelCellStyle()方法来设置单元格样式，具体步骤如下：
         * 创建HSSFCellStyle对象，即cellStyle。
         * 设置单元格边框样式，可以通过setBorderBottom()、setBorderLeft()、setBorderRight()和setBorderTop()方法分别设置下边框、左边框、右边框和上边框。同时，还可以通过setBottomBorderColor()方法设置边框颜色。
         * 设置文字居中，通过setAlignment()方法设置水平对齐方式，这里设置为居中。
         * 设置字体样式，首先创建HSSFFont对象font，然后通过setColor()、setFontName()、setFontHeightInPoints()、setBoldweight()、setWrapText()和setFillForegroundColor()方法分别设置字体颜色、字体样式、字体大小、加粗、自动换行和背景色。
         * 最后，将设置好的font应用到cellStyle上，并将cellStyle应用到单元格上。
         */
        //普通表头
        CellStyle normalStyle = workbook.createCellStyle();
        normalStyle.setBorderBottom(BorderStyle.THIN);// 设置下边框
        normalStyle.setBorderLeft(BorderStyle.THIN);// 设置左边框
        normalStyle.setBorderRight(BorderStyle.THIN);// 设置右边框
        normalStyle.setBorderTop(BorderStyle.THIN);// 设置上边框
        normalStyle.setAlignment(HorizontalAlignment.CENTER);
        normalStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        CellStyle normalStyle2 = workbook.createCellStyle();
        normalStyle2.setBorderBottom(BorderStyle.THIN);// 设置下边框
        normalStyle2.setBorderLeft(BorderStyle.THIN);// 设置左边框
        normalStyle2.setBorderRight(BorderStyle.THIN);// 设置右边框
        normalStyle2.setBorderTop(BorderStyle.THIN);// 设置上边框
        normalStyle2.setAlignment(HorizontalAlignment.RIGHT);
        normalStyle2.setVerticalAlignment(VerticalAlignment.CENTER);

        //必填项表头,设置为红色表头
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        CellStyle requiredStyle = workbook.createCellStyle();
        requiredStyle.setBorderBottom(BorderStyle.THIN);// 设置下边框
        requiredStyle.setBorderLeft(BorderStyle.THIN);// 设置左边框
        requiredStyle.setBorderRight(BorderStyle.THIN);// 设置右边框
        requiredStyle.setBorderTop(BorderStyle.THIN);// 设置上边框
        requiredStyle.setAlignment(HorizontalAlignment.CENTER);
        requiredStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        requiredStyle.setFont(font);

        // 获取第一个 Sheet
        Sheet sheet = workbook.createSheet("Sheet1");

        /*
         * 通过setColumnWidth方法设置列的宽度。
         * 参数分别为列索引和宽度值，宽度值以 1/256 个字符为单位。
         * 你可以根据需要设置不同列的宽度。
         */
        for (int i = 0; i < excelHeaders.size(); i++) {
            JSONObject json = excelHeaders.getJSONObject(i);
            int width = json.getIntValue("width");
            if(width>=5){
                sheet.setColumnWidth(i, width * 256);
            }
        }
        //创建表头
        int titleRowIndex = template.getIntValue("titleNo");

        if(titleRowIndex>=2){
            //第一行，合并信息
            Row headerRow0 = sheet.createRow(0);
            headerRow0.setHeightInPoints(18);
            Cell cell = headerRow0.createCell(0);
            cell.setCellValue(template.getString("label"));
            cell.setCellStyle(normalStyle);
            if(excelHeaders.size()>1){
                // 设置合并单元格的起始行
                CellRangeAddress cellRangeAddress = new CellRangeAddress(0,0,0,excelHeaders.size()-1);
                sheet.addMergedRegion(cellRangeAddress);
                RegionUtil.setBorderTop(BorderStyle.THIN,cellRangeAddress,sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN,cellRangeAddress,sheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN,cellRangeAddress,sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN,cellRangeAddress,sheet);
            }
        }

        if(titleRowIndex==3){
            //第一行，合并信息
            Row headerRow1 = sheet.createRow(1);
            headerRow1.setHeightInPoints(18);
            Cell cell = headerRow1.createCell(0);
            cell.setCellValue(template.getString("label"));
            cell.setCellStyle(normalStyle2);
            // 设置合并单元格的起始行
            if(excelHeaders.size()>1){
                // 设置合并单元格的起始行
                CellRangeAddress cellRangeAddress = new CellRangeAddress(1,1,0,excelHeaders.size()-1);
                sheet.addMergedRegion(cellRangeAddress);
                RegionUtil.setBorderTop(BorderStyle.THIN,cellRangeAddress,sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN,cellRangeAddress,sheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN,cellRangeAddress,sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN,cellRangeAddress,sheet);
            }
        }

        //创建列头
        Row titleRow = sheet.createRow(titleRowIndex-1);
        titleRow.setHeightInPoints(18);
        for (int i = 0; i < excelHeaders.size(); i++) {
            JSONObject json = excelHeaders.getJSONObject(i);
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(json.getString("label"));
            if("Y".equals(json.getString("isRequired"))){
                cell.setCellStyle(requiredStyle);
            }else{
                cell.setCellStyle(normalStyle);
            }
        }

        //获取第二个sheet页面
        Sheet sheet2 = workbook.createSheet("填写说明");

        sheet2.setColumnWidth(0, 10 * 256);
        sheet2.setColumnWidth(1, 25 * 256);
        sheet2.setColumnWidth(2, 15 * 256);
        sheet2.setColumnWidth(3, 10 * 256);
        sheet2.setColumnWidth(4, 25 * 256);
        sheet2.setColumnWidth(5, 50 * 256);
        //设备标题框
        Row titleRow2 = sheet2.createRow(0);
        titleRow2.setHeightInPoints(18);

        Cell cell20 = titleRow2.createCell(0);
        cell20.setCellValue("序号");
        cell20.setCellStyle(normalStyle);

        Cell cell21 = titleRow2.createCell(1);
        cell21.setCellValue("列名称");
        cell21.setCellStyle(normalStyle);

        Cell cell22 = titleRow2.createCell(2);
        cell22.setCellValue("数据类型");
        cell22.setCellStyle(normalStyle);

        Cell cell23 = titleRow2.createCell(3);
        cell23.setCellValue("必填");
        cell23.setCellStyle(normalStyle);

        Cell cell24 = titleRow2.createCell(4);
        cell24.setCellValue("字段格式");
        cell24.setCellStyle(normalStyle);

        Cell cell25 = titleRow2.createCell(5);
        cell25.setCellValue("说明");
        cell25.setCellStyle(normalStyle);

        //
        for (int i = 0; i < excelHeaders.size(); i++) {
            JSONObject json = excelHeaders.getJSONObject(i);
            Row row = sheet2.createRow(i+1);
            row.setHeightInPoints(18);
            //序号
            Cell cell0 = row.createCell(0);
            cell0.setCellValue(i+1);
            cell0.setCellStyle(normalStyle);
            //字段说明
            Cell cell1 = row.createCell(1);
            cell1.setCellValue(json.getString("label"));
            if("Y".equals(json.getString("isRequired"))){
                cell1.setCellStyle(requiredStyle);
            }else{
                cell1.setCellStyle(normalStyle);
            }

            Cell cell2 = row.createCell(2);
            if("Number".equals(json.getString("dataType"))){
                cell2.setCellValue("数值型");
            }else if("DateTime".equals(json.getString("dataType"))){
                cell2.setCellValue("日期时间");
            }else if("IdCard".equals(json.getString("dataType"))){
                cell2.setCellValue("身份证号");
            }else{
                cell2.setCellValue("字符型");
            }

            cell2.setCellStyle(normalStyle);

            Cell cell3 = row.createCell(3);
            if("Y".equals(json.getString("isRequired"))){
                cell3.setCellValue("Y");
                cell3.setCellStyle(requiredStyle);
            }else{
                cell3.setCellValue("N");
                cell3.setCellStyle(normalStyle);
            }


            Cell cell4 = row.createCell(4);
            cell4.setCellValue(json.getString("format"));
            cell4.setCellStyle(normalStyle);

            Cell cell5 = row.createCell(5);
            cell5.setCellValue(json.getString("remark"));
            cell5.setCellStyle(normalStyle);
        }


        //文件导出
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        byte[] bytes = outputStream.toByteArray();

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.add("file-name", URLEncoder.encode(template.getString("label")+"-导入模板.xlsx", StandardCharsets.UTF_8));
        headers.setContentDispositionFormData("attachment", URLEncoder.encode(template.getString("label")+"-导入模板.xlsx", StandardCharsets.UTF_8));
        return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
    }

    @RequestMapping("export")
    @ResponseBody
    public ResponseEntity<byte[]> templateExport(@RequestBody JSONArray params) {
        JSONArray array = new JSONArray();
        for(int i=0;i<params.size();i++){
            String templateCode = params.getJSONObject(i).getString("code");
            JSONObject template = dao.queryJSON("select * from sys_excel_template where code=?",templateCode);
            JSONArray templateFields = dao.queryJSONArray("select * from sys_excel_template_field where template_code=? order by sort_no",templateCode);
            template.put("templateFields", templateFields);
            array.add(template);
        }
        byte[] bytes = array.toJSONString().getBytes(StandardCharsets.UTF_8);

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
        String fileName = "Excel配置-"+ LocalDateTime.now().format(formatter)+".json";

        headers.add("file-name", URLEncoder.encode(fileName, StandardCharsets.UTF_8));
        headers.setContentDispositionFormData("attachment", URLEncoder.encode(fileName, StandardCharsets.UTF_8));
        return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
    }

    @RequestMapping("import")
    @Transactional
    @ResponseBody
    public ResultT templateImport(@RequestParam MultipartFile file, HttpServletRequest request) throws IOException {

        StringBuffer sb = new StringBuffer();
        BufferedReader reader = new BufferedReader(new InputStreamReader(file.getInputStream(), StandardCharsets.UTF_8));
        reader.lines().forEach(line -> {
            sb.append(line);
        });
        JSONArray array = JSONArray.parseArray(sb.toString());
        for(int i=0;i<array.size();i++){
            JSONObject json = array.getJSONObject(i);
            String templateCode = json.getString("code");
            //判定模板是否存在
            dao.executeUpdate("delete from sys_excel_template_field where template_code=?",templateCode);
            dao.executeUpdate("delete from sys_excel_template where code=?",templateCode);

            //插入模板信息
            json.put("id",IdWorker.ulid());
            dao.createJSON(json,"sys_excel_template");

            JSONArray templateFields = json.getJSONArray("templateFields");

            for(int j=0;j<templateFields.size();j++){
                JSONObject field = templateFields.getJSONObject(j);
                field.put("id",IdWorker.ulid());
                dao.createJSON(field,"sys_excel_template_field");
            }
        }

        return ResultT.success();
    }

}
